﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ebay.Dal.CenterDB_LTableAdapters;

namespace ebay.Dal
{
    public class ReturnsOrder
    {
        /// <summary>
        /// 中间平台同步K3退货单
        /// </summary>
        /// <param name="muser"></param>
        /// <returns></returns>

        public bool Add(string muser)
        {
            SqlParameter p = new SqlParameter("@muser", SqlDbType.VarChar);
            p.Value = muser;
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_fromk3_th", p);
                return true;
            }
            catch
            {
                return false;
            }
        }


        /// <summary>
        /// 退货单和订单的匹配操作
        /// </summary>
        /// <param name="ebayuserid"></param>
        /// <param name="orderId"></param>
        /// <param name="auser"></param>
        /// <param name="processingStatus"></param>
        /// <returns></returns>
        public int Match(string returnsOrderId, string ebayuserid, string orderId, int fitemId, string auser, int processingStatus, string remarks)
        {
            string updatetext = @"Update ReturnsOrder set IsMatch=1,OrderId=@OrderId ,EbayUserId=@EbayUserId ,ProcessingStatus=@ProcessingStatus,Remarks=@Remarks,
                                         Auser=@Auser,Atime=GETDATE () Where  ReturnsOrder.ReturnsOrderId =@RetunsOrderId and ReturnsOrder.FitemId=@FitemId and 
                                         exists (select (1) from Orders where OrderId=@OrderId and EbayUserId =@EbayUserId)";
            SqlParameter[] paras ={
                                      new SqlParameter ("@RetunsOrderId",SqlDbType .VarChar ),
                                      new SqlParameter ("@EbayUserId",SqlDbType .VarChar),
                                      new SqlParameter ("@OrderId",SqlDbType .VarChar),
                                      new SqlParameter ("@Auser",SqlDbType .VarChar),
                                      new SqlParameter ("@ProcessingStatus",SqlDbType .Int),
                                      new SqlParameter ("@Remarks",SqlDbType.VarChar),
                                      new SqlParameter("@FitemId",SqlDbType.Int)
                              };

            string cmdText = @"Update Orders set OrderStatusId =6,[Muser]=@Auser,[Mtime]=GETDATE() where EbayUserId =@EbayUserId and OrderId=@OrderId";
            SqlParameter[] p = {
                                      new SqlParameter ("@EbayUserId",SqlDbType .VarChar),
                                      new SqlParameter ("@OrderId",SqlDbType .VarChar),
                                      new SqlParameter ("@Auser",SqlDbType .VarChar)
                               };
            paras[0].Value =returnsOrderId ;
            paras[1].Value =ebayuserid ;
            paras[2].Value = orderId;
            paras[3].Value =auser ;
            paras[4].Value =processingStatus ;
            paras[5].Value = remarks;
            paras[6].Value = fitemId;

            p[0].Value = ebayuserid;
            p[1].Value = orderId;
            p[2].Value = auser;
            try
            {
                int val = 0;
                using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
                {
                    connection.Open();
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, updatetext, paras);//returnorder
                        val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);//orders                
                        try
                        {
                            trans.Commit();
                        }
                        catch
                        {
                            trans.Rollback();                           
                            return 0;
                        }
                    }
                }
                if (val > 0)
                {
                    return val;
                }
            }
            catch
            {
                throw;                
            }
            return 0;
        }
            
        //判断是否存在此商品。
        public int IsExists(string product)
        {
            string text = @"select Count(1) from Product where ProductId ='" + product + "'";
            int val = 0;
            try
            {
                 val = (int)SqlHelper.ExecuteScalar(SqlHelper.ceterDB_Conn, CommandType.Text, text,null);
            }
            catch
            {
                return 0;
            }
            return val;
        }

        //*****************************缺货商品替换********************************
        //获得替换的EbayUserId和OrderLineItemId
        public DataTable GetReplaceParas(string productId)
        {
//            string sqltext2 = @"select c.EbayUserId as EbayUserId ,c.OrderLineItemId as OrderLineItemId from CustomerResolve c,Product p,Orders o where p.ProductId =c.ProductId and  
//                                c.ProductId =@ProductId and o.IsPay ='Y' and (o.OrderStatusId =1 or o.OrderStatusId=3) and o.EbayUserId =c.EbayUserId and o.OrderLineItemId =c.OrderLineItemId";
            string sqltext = @" Select o.EbayUserId,os.OrderLineItemId,o.ItemId,o.Price,
	                            o.CustomLable,c.Listing,p.UserCode,p.ProductId,
	                            case when len(p2.ProductId2) > 0 then 1 else 0 end as pt
                                From EbayActiveListing o 
                                Left Outer Join CustomLableMapping c on c.CustomerId = dbo.ReplaceCustomLable(o.EbayUserId,o.CustomLable)
                                Left Outer Join Product p on Charindex
							                                (
								                                ']' + p.UserCode + '[',
								                                ']' + c.Listing
							                                ) > 0
                                Left Outer Join Product2 p2 on p.ProductId = p2.ProductId
                                left Outer Join Orders os on o.CustomLable =os.CustomLable 
                                where p.ProductId =@ProductId and os.IsPay ='Y' and (os.OrderStatusId =1 or os.OrderStatusId=3)";
            SqlParameter p = new SqlParameter("@ProductId", SqlDbType.VarChar);
            p.Value = productId;
            DataTable dt = new DataTable();
            try
            {
                dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, sqltext, p);
            }
            catch
            {
                return null; 
            }
            return  dt;
        }

        //****缺货商品替换****
        public bool ProductReplace(DataTable dt, decimal nQuantity, decimal oQuantity, string oProductId, string nProductId, string userId, ref string Msg)
        {
            string sqlWhere = " Where ";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sqlWhere += "(EbayUserId = '" + dt.Rows[i][0].ToString () + "' And OrderLineItemId = '" + dt .Rows [i][1].ToString ()+"' And ProductId = '" + oProductId + "') Or";
                i++;
            }
            sqlWhere = sqlWhere.Remove(sqlWhere.Length - 3);
            string serId = DBUtility.GetSrialNumber(DateTime.Now, "Rep");
            if (string.IsNullOrEmpty(serId))
            {
                Msg = "流水号获取失败";
                return false;
            }
           
            string cmdText="Insert Into LackProductReplace(SerialId,EbayUserId,OrderLineItemId,OldProductId,NewProductId,OldQuantity,NewQuantity,ReplaceDate,ReplaceUser)(Select '" + serId + "' as SerId,EbayUserId,OrderLineItemId,'" + oProductId +
                              "' as OldProductId,'" + nProductId + "' as NewProductId,Quantity,ceiling(Quantity* " + nQuantity / oQuantity + ") as NewQuantity,GetDate() As ReplaceDate,'" + userId + "' From CustomerResolve " + sqlWhere + " )";
            string cmdText1 = "Update CustomerResolve Set ProductId = '" + nProductId + "',Muser='" + userId + "',Mtime=GETDATE(), Quantity = Ceiling(Quantity*" + nQuantity / oQuantity + ") " + sqlWhere;
            string cmdtext2 = @"Update ProductTracking set Recommendations =3,Muser='" + userId + "',Mtime =GETDATE() where ProductId ='" + oProductId + "'";
         
            try
            {
                int val = 0;
                using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
                {
                    connection.Open();
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, null);//LackProductReplace
                        val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText1, null);//CustomerResolve
                        val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdtext2, null);//producttracking
                        try
                        {
                            trans.Commit();
                        }
                        catch
                        {
                            trans.Rollback();
                            Msg = "服务器错误";
                            return false;
                        }
                    }
                }
                if (val > 0)
                {
                    return true;
                }
            }
            catch
            {
                Msg = "服务器错误";              
            }
            return false;          
        }

        //************************************修改最大listing*************************        
        public bool UpDateStock(string productid, decimal num, string userid, ref string Msg)
        {
            SqlParameter[] paras ={
                                      new SqlParameter ("@ProductId",SqlDbType.VarChar),
                                      new SqlParameter ("@FQty",SqlDbType .Decimal),
                                      new SqlParameter ("@Muser",SqlDbType .VarChar)
                                 };
            paras[0].Value = productid;
            paras[1].Value = num;
            paras[2].Value = userid;
            try
            {
                int val = SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_ProductTracking_Listing", paras);
                if (val > 0)
                {
                    return true;
                }
            }
            catch
            {
                Msg = "服务器错误！...";
                throw;
            }
            return false;
        }

        //***************************************盘亏单写入K3..*****************************************      
        public bool Shortage(string productId, int fEmptId,int fStockId,int fItemId, decimal fQty,string userId, ref string Msg)
        {
            //修改缺货表
            string sqltext = @"Update CenterDB .dbo.ProductTracking set Recommendations =4,Muser=@Muser,Mtime =GETDATE() Where ProductId =@ProductId ";
            SqlParameter [] p = {                                 
                                 new SqlParameter("@ProductId",SqlDbType.VarChar),
                                 new SqlParameter("@Muser",SqlDbType.VarChar)
                             };
            p[0].Value = productId;
            p[1].Value = userId;

            //盘亏单写K3
            string serId = DBUtility.GetSrialNumber(DateTime.Now, "PKD");
            if (string.IsNullOrEmpty(serId))
            {
                Msg = "流水号获取失败";
                return false;
            }
            DateTime fdate = DateTime.Now;

            SqlParameter[] paras ={
                                      new SqlParameter ("@BillNO",SqlDbType.VarChar),
                                      new SqlParameter ("@FDate",SqlDbType.DateTime),
                                      new SqlParameter ("@FEmpID",SqlDbType.Int),
                                      new SqlParameter ("@FStockID",SqlDbType.Int),
                                      new SqlParameter ("@FItemID",SqlDbType.Int),
                                      new SqlParameter ("@FQty",SqlDbType.Decimal)
                                 };
           
            // fEmptId = 28149;           
            paras[0].Value = serId;
            paras[1].Value = fdate;
            paras[2].Value = fEmptId;
            paras[3].Value = fStockId;
            paras[4].Value = fItemId;
            paras[5].Value = fQty;
            try
            {
                int val = 0;
                using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
                {
                    connection.Open();
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        try
                        {
                            val += SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_PKD", paras);//写K3
                            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqltext, p);//修改缺货表   
                            trans.Commit();
                        }
                        catch
                        {
                            trans.Rollback();
                            return false;
                        }
                        if (val > 0)
                        {
                            return true;
                        }
                    }
                }            
            }
            catch(Exception ex)
            {
                ex.ToString();
                Msg = "服务器错误！...";
                throw;
            }
            return false;
        }
        //************************************************************************************
      
    }
}